package com.elm.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.elm.dao.BusinessDao;
import com.elm.po.Business;
import com.elm.po.Orders;
import com.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao {
	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;

	@Override
	public List<Business> listBusinessByOrderTypeId(Integer orderTypeId) throws Exception { // 商家类型编号
		List<Business> list = new ArrayList<>();

		String sql = "select * from business where orderTypeId=? order by businessId";

		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, orderTypeId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Business business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessImg(rs.getString("businessImg"));
				business.setOrderTypeId(rs.getInt("orderTypeId"));
				business.setStarPrice(rs.getDouble("starPrice"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setRemarks(rs.getString("remarks"));
				list.add(business);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list;
	}

	public List<Business> listAllBusiness(Integer orderTypeId) throws Exception {
		List<Business> list1 = new ArrayList<>();
		String sql = "SELECT \r\n"
				+ "    b.*, \r\n"
				+ "    AVG(o.star) AS avg_star\r\n"
				+ "FROM \r\n"
				+ "    elm.business b \r\n"
				+ "LEFT JOIN \r\n"
				+ "    elm.orders o ON o.businessId = b.businessId\r\n"
				+ "GROUP BY \r\n"
				+ "    b.businessId  \r\n"
				+ "\r\n"
				+ "UNION\r\n"
				+ "\r\n"
				+ "SELECT \r\n"
				+ "    b.*, \r\n"
				+ "    NULL AS avg_star  \r\n"
				+ "FROM \r\n"
				+ "    elm.business b\r\n"
				+ "WHERE \r\n"
				+ "    b.businessId NOT IN (SELECT DISTINCT businessId FROM elm.orders)\r\n"
				+ "ORDER BY \r\n"
				+ "    businessId;";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Business business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessImg(rs.getString("businessImg"));
				business.setOrderTypeId(rs.getInt("orderTypeId"));
				business.setStarPrice(rs.getDouble("starPrice"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setRemarks(rs.getString("remarks"));
				business.setMonthlySales(rs.getInt("monthlySales"));
				business.setAvg_star(rs.getDouble("avg_star"));
				list1.add(business);	
				}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list1;
	}
	
	public List<Business> listBusinessesBySales(Integer orderTypeId) throws Exception {
		List<Business> list2 = new ArrayList<>();
		String sql = "SELECT \r\n"
				+ "    b.*, \r\n"
				+ "    AVG(o.star) AS avg_star\r\n"
				+ "FROM \r\n"
				+ "    elm.business b \r\n"
				+ "LEFT JOIN \r\n"
				+ "    elm.orders o ON o.businessId = b.businessId\r\n"
				+ "GROUP BY \r\n"
				+ "    b.businessId  \r\n"
				+ "\r\n"
				+ "UNION\r\n"
				+ "\r\n"
				+ "SELECT \r\n"
				+ "    b.*, \r\n"
				+ "    NULL AS avg_star  \r\n"
				+ "FROM \r\n"
				+ "    elm.business b\r\n"
				+ "WHERE \r\n"
				+ "    b.businessId NOT IN (SELECT DISTINCT businessId FROM elm.orders)\r\n"
				+ "ORDER BY \r\n"
				+ "    monthlySales DESC;";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Business business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessImg(rs.getString("businessImg"));
				business.setOrderTypeId(rs.getInt("orderTypeId"));
				business.setStarPrice(rs.getDouble("starPrice"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setRemarks(rs.getString("remarks"));
				business.setMonthlySales(rs.getInt("monthlySales"));
				business.setAvg_star(rs.getDouble("avg_star"));
				list2.add(business);
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return list2;
	}

	@Override
	public Business getBusinessById(Integer businessId) throws Exception { // 商家编号
		Business business = null;
		String sql = "select * from business where businessId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);
			rs = pst.executeQuery();
			while (rs.next()) {
				business = new Business();
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessImg(rs.getString("businessImg"));
				business.setOrderTypeId(rs.getInt("orderTypeId"));
				business.setStarPrice(rs.getDouble("starPrice"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setRemarks(rs.getString("remarks"));
			}
		} finally {
			DBUtil.close(rs, pst);
		}
		return business;
	}
	
	public int salesAddById(Integer businessId) throws Exception { // 商家编号
		int result = 0;
		String sql = "Update business set monthlySales = monthlySales+1 where businessId=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);
			result = pst.executeUpdate();
		} finally {
			DBUtil.close(rs, pst);
		}
		return result;
	}
}